insert overwrite table jms_dm.dm_customer_new_sign_detail_dt partition (dt)
select networktype                               --网点类型,区分代理,加盟,网点
     , timetype                                  --时间类型,区分日,周,月
     , date_time                                 --日期
     , agent_code                                --代理区名
     , agent_name                                --代理区名
     , fran_code                                 --加盟商code
     , fran_name                                 --加盟商名
     , network_code                              --寄件网点
     , network_name                              --网点编码
     , customer_code                             --客户code
     , customer_name                             --客户名称
     , customer_create_time                      --客户签约时间
     , first_dilever_time
     , case
           when ticket_quantity / timenum_new_sign > 5000 then 'A'
           when ticket_quantity / timenum_new_sign > 1000 and
                ticket_quantity / timenum_new_sign <= 5000 then 'B'
           when ticket_quantity / timenum_new_sign > 500 and
                ticket_quantity / timenum_new_sign <= 1000 then 'C'
           when ticket_quantity / timenum_new_sign > 300 and
                ticket_quantity / timenum_new_sign <= 500 then 'D'
           when ticket_quantity / timenum_new_sign >= 0 and ticket_quantity / timenum_new_sign <= 300
               then 'E'
           else '其他'
    end as customer_level                        --客户等级
     , round(ticket_quantity / timenum_new_sign) --票量
     , dorisdt
     , dt
from (
         select networktype          --网点类型,区分代理,加盟,网点
              , timetype             --时间类型,区分日,周,月
              , date_time            --日期
              , agent_code           --代理区名
              , agent_name           --代理区名
              , fran_code            --加盟商code
              , fran_name            --加盟商名
              , network_code         --寄件网点
              , network_name         --网点编码
              , customer_code        --客户code
              , customer_name        --客户名称
              , customer_create_time --客户签约时间
              , customer_create_time as first_dilever_time
              , ticket_quantity      --票量
              , case
                    when timetype = 1 then 1
                    when timetype = 2 then if(date_time = concat(year(next_day(DATE_ADD('{{ execution_date | cst_ds }}', -7), 'MO')), '-',
                                                                 if(
                                                                         length(cast(weekofyear('{{ execution_date | cst_ds }}') as string)) < 2,
                                                                         concat('0', cast(weekofyear('{{ execution_date | cst_ds }}') as string)),
                                                                         cast(weekofyear('{{ execution_date | cst_ds }}') as string))), datediff('{{ execution_date | cst_ds }}',customer_create_time)+1,
                                              (7 - if(dayofweek(customer_create_time) = 1, 7,
                                                      dayofweek(customer_create_time))) + 1)
                    when timetype = 3 then if(date_time = substr('{{ execution_date | cst_ds }}', 1, 7),
                                                     datediff('{{ execution_date | cst_ds }}', customer_create_time) + 1,
                                                     datediff(add_months(concat(substr(customer_create_time, 1, 7), '-', '01'), 1),
                                                              concat(substr(date_time, 1, 7), '-', '01')) - dayofmonth(substr(customer_create_time, 1, 10)) + 1)
             end                     as timenum_new_sign
              , dorisdt
              , dorisdt              as dt
         from jms_dm.dm_customer_date_network_level_ticket_detail_dt
         where equalflag = 1
     ) t

 distribute by dt;
